﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;


namespace mpsalary.App_Code
{
    public class User
    {
        public String Usename { get; set; }
        public String FirstName { get; set; }
        public String LastName { get; set; }
        public String Salutation { get; set; }
        public String Gender { get; set; }
        public String Occupation { get; set; }
        public String School { get; set; }
        public String Company { get; set; }
        public String DOB { get; set; }
        public String NRIC { get; set; }
        public String Email { get; set; }
        public String Password { get; set; }
        public String MobileNo { get; set; }

        //----------------------------------------
        // create user- when a new user sign up
        //----------------------------------------


        public void CreateUser()
        {
            //INSERT INTO...

            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();

                //2.open connection -> command
                conn.Open();
                cmd.Connection = conn;

                //3.take values from textbox and execute sql command

                cmd.CommandText = "INSERT INTO [User](Usename, Email, Password)VALUES(@Usename, @Email, @Password)";
                cmd.Parameters.AddWithValue("@Usename", Usename);
                cmd.Parameters.AddWithValue("@Email", Email);
                cmd.Parameters.AddWithValue("@Password", Password);
                cmd.ExecuteNonQuery();

                Debug.WriteLine("Sql Command Completed");

            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;

            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }

        }




        ////---------------------------------------
        //Retrieve Student Password for main page
        ////---------------------------------------
        public void RetrievePassword()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                //2.open connection -> command
                conn.Open();
                comm.Connection = conn;

                comm.CommandText = "SELECT Password From [User] Where Usename = @Usename";
                comm.Parameters.AddWithValue("@Usename", Usename);

                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {
                    Password = dr["Password"].ToString();
                }


            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;
            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }

        }

        ////---------------------------------------
        //Retrieve User Information on Update Particulars Page
        ////---------------------------------------
        public void RetrieveUserInfo(string Usename)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                conn.Open();

                comm.Connection = conn;

                comm.CommandText = "Select Salutation,LastName,FirstName,Occupation,Company,School,Email,NRIC,MobileNumber,DOB from [User] Where Usename = @Usename";
                comm.Parameters.AddWithValue("@Usename", Usename);
                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {
                    Salutation = dr["Salutation"].ToString();
                    //Gender = dr["Gender"].ToString();
                    LastName = dr["LastName"].ToString();
                    FirstName = dr["FirstName"].ToString();
                    Occupation = dr["Occupation"].ToString();
                    Company = dr["Company"].ToString();
                    School = dr["School"].ToString();
                    Email = dr["Email"].ToString();
                    //Password = dr["Password"].ToString();
                    //ChangePw = dr["ChangePw"].ToString();
                    //ChangeNewPassword = dr["ChangeNewPassword"].ToString();
                    NRIC = dr["NRIC"].ToString();
                    MobileNo = dr["MobileNumber"].ToString();
                    DOB = dr["DOB"].ToString();

                }


            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }



        }
        //------------------------------------------------
        // Update User 
        //------------------------------------------------
        public void UpdateUser()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                conn.Open();

                comm.Connection = conn;
                comm.CommandText = "UPDATE [User] SET FirstName=(@FirstName), LastName=(@LastName),Gender=(@Gender),Salutation=(@Salutation), Occupation=(@Occupation), School=(@School), Company=(@Company), DOB=(@DOB), NRIC=(@NRIC), Email=(@Email),  MobileNumber=(@MobileNumber) Where Usename = @Usename";
                comm.Parameters.AddWithValue("@Usename", Usename);
                comm.Parameters.AddWithValue("@FirstName", FirstName);
                comm.Parameters.AddWithValue("@LastName", LastName);
                comm.Parameters.AddWithValue("@Gender", Gender);
                comm.Parameters.AddWithValue("@Salutation", Salutation);
                comm.Parameters.AddWithValue("@Occupation", Occupation);
                comm.Parameters.AddWithValue("@NRIC", NRIC);
                comm.Parameters.AddWithValue("@School", School);
                comm.Parameters.AddWithValue("@Company", Company);
                comm.Parameters.AddWithValue("@DOB", DOB);
                comm.Parameters.AddWithValue("@Email", Email);
                //comm.Parameters.AddWithValue("@Password", Password);
                //comm.Parameters.AddWithValue("@ChangePw", ChangePw);
                //comm.Parameters.AddWithValue("@ChangeNewPassword", ChangeNewPassword);
                comm.Parameters.AddWithValue("@MobileNumber", MobileNo);
                comm.ExecuteNonQuery();


            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();

            }



        }
        //---------------------------------------------------------------------------------------------
        //STUDENTCHANGEPASSWORD
        //---------------------------------------------------------------------------------------------
        public void ChangePassword()
        {
            //SELECT FROM ...

            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();

                //2.open connection -> command
                conn.Open();
                cmd.Connection = conn;

                //3.execute sql and read values
                cmd.CommandText = "UPDATE [User] SET Password =@Password WHERE Usename=@Usename";
                cmd.Parameters.AddWithValue("@Password ", Password);
                cmd.Parameters.AddWithValue("@Usename", Usename);
                cmd.ExecuteNonQuery();



            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;

            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }
        }

        public void RetrieveUserPassword()
        {
            //SELECT FROM ...

            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();

                //2.open connection -> command
                conn.Open();
                cmd.Connection = conn;

                //3.execute sql and read values
                cmd.CommandText = "SELECT Password FROM User WHERE Usename = @Usename";
                cmd.Parameters.AddWithValue("@Usename", Usename);
                SqlDataReader dr = cmd.ExecuteReader();


                //read each row
                while (dr.Read())
                {
                    Password = dr["Password"].ToString();
                }


            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;

            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }



        }

        //-------------------------------------------------------
        //User delete user
        //-------------------------------------------------------
        public void DeleteUser()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                conn.Open();

                comm.Connection = conn;
                comm.CommandText = " DELETE FROM [User] WHERE Usename = @Usename";
                comm.Parameters.AddWithValue("@Usename", Usename);
                comm.ExecuteReader();

            }
            catch (SqlException ex)
            {

                throw ex;
            }
            finally
            {

                conn.Close();
            }

        }

        public void CheckEmail()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                comm.Connection = conn;
                conn.Open();

                comm.CommandText = "Select Email from [User] ";
                //comm.Parameters.AddWithValue("@Usename", Usename);
                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {
                    Email = dr["Email"].ToString();
                }



            }
            catch(SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void CheckUsername()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                comm.Connection = conn;
                conn.Open();

                comm.CommandText = "Select Usename from [User] ";
                //comm.Parameters.AddWithValue("@Usename", Usename);
                SqlDataReader dr = comm.ExecuteReader();

                //this is to read each row in the database
                while (dr.Read())
                {
                    Usename = dr["Usename"].ToString();
                }



            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }



        //----------------------------------------------
        //retrieve username and password for forget password
        //----------------------------------------------


        public void RetrieveForgetPassword()
        {
            //SELECT FROM ...

            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();

            try
            {
                // Executes SQL
                //1.get connection string from db
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();

                //2.open connection -> command
                conn.Open();
                cmd.Connection = conn;

                //3.execute sql and read values
                cmd.CommandText = "SELECT Password, Usename FROM [User] WHERE Email = @Email";
                cmd.Parameters.AddWithValue("@Email", Email);
                SqlDataReader dr = cmd.ExecuteReader();


                //read each row
                while (dr.Read())
                {
                    Password = dr["Password"].ToString();
                    Usename = dr["Usename"].ToString();
                }


            }
            catch (SqlException ex)
            {
                //If there is an error with the database
                throw ex;

            }
            finally
            {
                // no matter what, run this
                conn.Close();

            }



        }


    }
}